// <copyright file="ScriptToSSG.cs" company="DotNetSide">
// Copyright (c) 2008 All Right Reserved
// </copyright>
// <author>Fabio.Cozzolino</author>
// <date>07/07/2008 18.00.57</date>

namespace DotNetSide.SqlScriptGenerator.Management
{
    using System;
    using System.Collections.Generic;
    using System.Text;
    using Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer;
    using EnvDTE;
    using System.Diagnostics;
    using System.Windows.Forms;
    using Microsoft.SqlServer.Management.UI.VSIntegration;
    using Microsoft.SqlServer.Management.UI.VSIntegration.Editors;
    using Microsoft.SqlServer.Management.Smo.RegSvrEnum;
    using System.Data.SqlClient;
    using System.Data;
    using EnvDTE80;

    internal class ScriptToSSG : ToolsMenuItemBase
    {
        private DTE2 applicationObject;

        private string server, database, table;
        public string Server
        {
            get { return this.server; }
            set { this.server = value; }
        }

        public string Database
        {
            get { return this.database; }
            set { this.database = value; }
        }

        public string Table
        {
            get { return this.table; }
            set { this.table = value; }
        }

        public ScriptToSSG(DTE2 applicationObject, string text)
            : base()
        {
            this.applicationObject = applicationObject;
            this.Text = text;// "Backup to script with Sql Script Generator";
        }

        protected override void Invoke()
        {
            IScriptFactory scriptFactory = ServiceCache.ScriptFactory;
            UIConnectionInfo connectionInfo = scriptFactory.CurrentlyActiveWndConnectionInfo.UIConnectionInfo;

            string generatedScript = this.GenerateScript(connectionInfo);
            if (generatedScript != null && generatedScript.Length > 0)
            {
                this.AddScriptToCurrentEnvironment(scriptFactory, connectionInfo, generatedScript);
            }
        }

        public override object Clone()
        {
            return this;
        }

        private void AddScriptToCurrentEnvironment(IScriptFactory scriptFactory, UIConnectionInfo connectionInfo, string generatedScript)
        {
            scriptFactory.CreateNewBlankScript(ScriptType.Sql, connectionInfo, null);

            EnvDTE.Document objEnvDocument;
            EnvDTE.TextDocument objTextDocument;
            EnvDTE.EditPoint objEndPoint;

            objEnvDocument = applicationObject.ActiveDocument;
            objTextDocument = (EnvDTE.TextDocument)objEnvDocument.Object(null);
            objEndPoint = objTextDocument.EndPoint.CreateEditPoint();
            objEndPoint.Insert(generatedScript);
        }

        private string GenerateScript(UIConnectionInfo connectionInfo)
        {
            string generatedScript = null;

            string connectionString = this.GetConnectionString(connectionInfo);
            string[] tables;
            if (this.table == null || this.table.Length == 0)
            {
                tables = this.GetTables(connectionString);
            }
            else
            {
                tables = new string[] { this.table };
            }

            SqlGeneratorOptions options = new SqlGeneratorOptions();
            options.GenerateDeleteStatement = false;
            options.QueryType = TypeOfQuery.InsertOrUpdate;
            options.Tables = tables;

            SqlGeneratorOptionsWindow optionWindow = new SqlGeneratorOptionsWindow();
            optionWindow.SSGPropertyGrid.SelectedObject = options;
            if (optionWindow.ShowDialog() == DialogResult.OK)
            {
                SqlGenerator sqlgen = new SqlGenerator(connectionString, this.database, options);
                try
                {
                    sqlgen.Generate();
                    generatedScript = sqlgen.Script;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                } 
            }
            return generatedScript;
        }

        private string[] GetTables(string connectionString)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();
            DataTable schema = conn.GetSchema("Tables", new string[] { this.database, null, null, "BASE TABLE" });
            conn.Close();

            string[] tables = new string[schema.Rows.Count];
            int i = 0;
            foreach (DataRow table in schema.Rows)
            {
                tables[i++] = (string)table[2];
            }
            return tables;
        }

        private string GetConnectionString(UIConnectionInfo connectionInfo)
        {
            string connectionString;
            if (connectionInfo.AuthenticationType == 0)
            {
                // autenticazione windows
                connectionString = "Data Source=" + connectionInfo.ServerName + ";Initial Catalog=" + this.database + ";Integrated Security=SSPI";
            }
            else
            {
                connectionString = "Data Source=" + connectionInfo.ServerName + ";Initial Catalog=" + this.database + ";User ID=" + connectionInfo.UserName + ";Password=" + connectionInfo.Password;
            }
            return connectionString;
        }

        private void InsertToNewWindow(string script) 
        {
            IScriptFactory scriptFactory = ServiceCache.ScriptFactory;
            scriptFactory.CreateNewBlankScript(ScriptType.Sql, 
                scriptFactory.CurrentlyActiveWndConnectionInfo.UIConnectionInfo, null); 
        }
    }

    internal class ScriptToSSGMenu : ToolsMenuItemBase
    {
        private DTE2 applicationObject;

        private string server, database, table;
        public string Server
        {
            get { return this.server; }
            set { this.server = value; }
        }

        public string Database
        {
            get { return this.database; }
            set { this.database = value; }
        }

        public string Table
        {
            get { return this.table; }
            set { this.table = value; }
        }

        public ScriptToSSGMenu(DTE2 applicationObject)
            : base()
        {
            this.applicationObject = applicationObject;
            this.Text = "Backup to script with Sql Script Generator";
            this.AddChild(String.Empty, new ScriptToSSG(applicationObject, "Insert Only"));
        }

        protected override void Invoke()
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public override object Clone()
        {
            throw new Exception("The method or operation is not implemented.");
        }
    }
}
